#coding=utf-8
from datetime import datetime as dt

from sqlalchemy import Column, DateTime, Float, ForeignKey, Integer, Unicode, Text
from sqlalchemy.orm import relationship
from ext.model import DBSession, DeclarativeBase
from ext.model_base import StaticMixin, DynamicMixin
from ext.model_tree import TreeMixin, BranchMixin

class City(DeclarativeBase, DynamicMixin, TreeMixin, BranchMixin):

    __tablename__ = 'log_master_city'
    __classname__ = 'City'
    __branch__ = {'alpha':['Alpha City', 'alpha', 'alpha_order']}

    active_order = None

    alpha = Column(Integer)
    alpha_order = Column(Integer)
    beta = Column(Integer)
    beta_order = Column(Integer)
    gamma = Column(Integer)
    gamma_order = Column(Integer)

    parent_id = Column(Integer)

    @classmethod
    def find_top_cities(cls):
        return DBSession.query(cls).filter(cls.active==1).filter(cls.level==1).order_by(cls.lft).all()

    @classmethod
    def find_sub_cities(cls, parent_id):
        return DBSession.query(cls).filter(cls.active==1).filter(cls.parent_id==parent_id).order_by(cls.lft).all()

class Category(DeclarativeBase, DynamicMixin, TreeMixin):
    __tablename__ = 'log_master_category'

    parent_id = Column(Integer, ForeignKey('log_master_category.id'))
    parent = relationship('Category', primaryjoin='Category.parent_id == Category.id')

class CargoType(DeclarativeBase, DynamicMixin, BranchMixin):
    __tablename__ = 'log_master_cargo_type'
    __branch__ = {'active':['Cargo Type', 'active', 'active_order']}

class CargoPackage(DeclarativeBase, DynamicMixin, BranchMixin):
    __tablename__ = 'log_master_cargo_package_type'
    __branch__ = {'active':['Cargo Package', 'active', 'active_order']}

class CargoPayment(DeclarativeBase, DynamicMixin, BranchMixin):
    __tablename__ = 'log_master_cargo_payment_type'
    __branch__ = {'active':['Cargo Payment', 'active', 'active_order']}

class TransType(DeclarativeBase, DynamicMixin, BranchMixin):
    __tablename__ = 'log_master_trans_type'
    __branch__ = {'active':['Trans Type', 'active', 'active_order']}

class VehicleType(DeclarativeBase, DynamicMixin, BranchMixin):
    __tablename__ = 'log_master_vehicle_type'
    __branch__ = {'active':['Vehicle Type', 'active', 'active_order']}

class VehicleBody(DeclarativeBase, DynamicMixin, BranchMixin):
    __tablename__ = 'log_master_vehicle_body'
    __branch__ = {'active':['Vehicle Body', 'active', 'active_order']}

class Cargo(DeclarativeBase, DynamicMixin):
    __tablename__ = 'log_cargo'

    cargo_name = Column(Unicode(100))
    volume = Column(Float)
    weight = Column(Float)
    quantity = Column(Float)
    valid_start_date = Column(DateTime, default=dt.now)
    valid_end_date = Column(DateTime, default=dt.now)
    contact_name = Column(Unicode(20))
    contact_phone = Column(Unicode(50))
    description = Column(Text)

    start_city_id = Column(Integer, ForeignKey(City.id))
    start_city = relationship('City', primaryjoin=start_city_id == City.id)
    end_city_id = Column(Integer, ForeignKey(City.id))
    end_city = relationship('City', primaryjoin=end_city_id == City.id)
    cargo_category_id = Column(Integer, ForeignKey(Category.id))
    cargo_category = relationship('Category', primaryjoin=cargo_category_id == Category.id)
    transfer_type_id = Column(Integer, ForeignKey(Category.id))
    transfer_type = relationship('Category', primaryjoin=transfer_type_id == Category.id)
    transfer_demand_id = Column(Integer, ForeignKey(Category.id))
    transfer_demand = relationship('Category', primaryjoin=transfer_demand_id == Category.id)

# add vchicle first, then post vehicle line information
class Vehicle(DeclarativeBase, DynamicMixin):
    __tablename__ = 'log_vehicle'

    vehicle_no = Column(Unicode(20))
    vehicle_type_id = Column(Integer, ForeignKey(Category.id))
    vehicle_type = relationship('Category', primaryjoin=vehicle_type_id == Category.id)
    vehicle_body_id = Column(Integer, ForeignKey(Category.id))
    vehicle_body = relationship('Category', primaryjoin=vehicle_body_id == Category.id)
    vehicle_long = Column(Float)
    vehicle_load = Column(Float)
    contact_name = Column(Unicode(20))
    contact_phone = Column(Unicode(50))
    description = Column(Text)

    start_city_id = Column(Integer, ForeignKey(City.id))
    start_city = relationship('City', primaryjoin=start_city_id == City.id)
    end_city_id = Column(Integer, ForeignKey(City.id))
    end_city = relationship('City', primaryjoin=end_city_id == City.id)

class VehicleLine(DeclarativeBase, DynamicMixin):
    __tablename__ = 'log_vehicle_line'

    valid_start_date = Column(DateTime, default=dt.now)
    valid_end_date = Column(DateTime, default=dt.now)
    start_city_id = Column(Integer, ForeignKey(City.id))
    start_city = relationship('City', primaryjoin=start_city_id == City.id)
    end_city_id = Column(Integer, ForeignKey(City.id))
    end_city = relationship('City', primaryjoin=end_city_id == City.id)
    contact_name = Column(Unicode(20))
    contact_phone = Column(Unicode(50))
    description = Column(Text)

class Company(DeclarativeBase, DynamicMixin):
    __tablename__ = 'log_company'

    name = Column(Unicode(100))
    title = Column(Unicode(100))
    description = Column(Text)
    contact_name = Column(Unicode(20))
    contact_phone = Column(Unicode(50))
    contact_address = Column(Unicode(50))
    work_start_date = Column(DateTime, default=dt.now)
    work_end_date = Column(DateTime, default=dt.now)

    city_id = Column(Integer, ForeignKey(City.id))
    city = relationship('City', primaryjoin=city_id == City.id)
    business_type_id = Column(Integer, ForeignKey(Category.id))
    business_type = relationship('Category', primaryjoin=business_type_id == Category.id)

class Line(DeclarativeBase, DynamicMixin):
    __tablename__ = 'log_line'

